﻿using GuanYijia.Application.ProcurementManagement.ProcurementDTO;
using GuanYijia.DoMain.PageDto;
using GuanYijia.DoMain;
using GuanYijia.Repository;
using Panda.DynamicWebApi;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using GuanYijia.Application.OutPut;
using GuanYijia.Application.IntPut;
using Microsoft.AspNetCore.Mvc;
using Panda.DynamicWebApi.Attributes;
using GuanYijia.DoMain.RBAC;
using GuanYiJia.WebApi;
using Microsoft.AspNetCore.StaticFiles;
using NPOI.SS.UserModel;
using NPOI.XSSF.UserModel;
using Microsoft.Extensions.Logging;

namespace GuanYijia.Application.SalesManagement
{
    [DynamicWebApi]
    [ApiExplorerSettings(GroupName = "v1")]
    public class SalesDataService : ControllerBase, IDynamicWebApi
    {

        private readonly IBaseRepository<Sales_Orders> _salesOrderRepository;
        private readonly IBaseRepository<Documents> _documentsRepository;
        private readonly IBaseRepository<Supplier> _supplierRepository;
        private readonly IBaseRepository<ProductInfo> _productInfoRepository;
        private readonly IBaseRepository<Creator> _creatorRepository;
        private readonly IBaseRepository<Warehouse> _warehouseRepository;
        private readonly IBaseRepository<Account> _accountRepository;
        private readonly IBaseRepository<User> _userRepository;
        private readonly IBaseRepository<Sales> _salesRepository; 
        private readonly IBaseRepository<Customer> _customerRepository;
        private readonly IBaseRepository<Requisition> _requisitionRepository;
        private readonly IBaseRepository<Depot_Item> _depotitemRepository;
        private readonly IBaseRepository<PurchaseOrder> _purchaseOrderRepository;






        public SalesDataService(IBaseRepository<Sales_Orders> salesOrderRepository,
                                    IBaseRepository<Documents> documentsRepository,
                                    IBaseRepository<Supplier> supplierRepository,
                                    IBaseRepository<ProductInfo> productInfoRepository, IBaseRepository<Creator> creatorRepository, IBaseRepository<Warehouse> warehouseRepository, IBaseRepository<Account> accountRepository, IBaseRepository<User> userRepository, IBaseRepository<Sales> salesRepository, IBaseRepository<Customer> customerRepository, IBaseRepository<Requisition> requisitionRepository, IBaseRepository<Depot_Item> depotitemRepository, IBaseRepository<PurchaseOrder> purchaseOrderRepository)
        {
            _salesOrderRepository = salesOrderRepository;
            _documentsRepository = documentsRepository;
            _supplierRepository = supplierRepository;
            _productInfoRepository = productInfoRepository;
            _creatorRepository = creatorRepository;
            _warehouseRepository = warehouseRepository;
            _accountRepository = accountRepository;
            _userRepository = userRepository;
            _salesRepository = salesRepository;
            _customerRepository = customerRepository;
            _requisitionRepository = requisitionRepository;
            _depotitemRepository = depotitemRepository;
            _purchaseOrderRepository = purchaseOrderRepository;
           
        }
        /// <summary>
        /// 客户信息下拉框
        /// </summary>
        /// <returns></returns>
        [HttpGet]
        [SkipCustomFilterAttribute]
        public async Task<IQueryable<Customer>> GetSupplierList()
        {
            var list= await _customerRepository.GetAllAsync();
            return list.AsQueryable();
        }
       
        /// <summary>
        /// 销售订单列表
        /// </summary>
        /// <param name="input"></param>
        /// <returns></returns>
        [HttpPost]
        [SkipCustomFilterAttribute]
        public async Task<PageResultDto<SalesOrderOutput>> GetSalesOrderShow(SalesOrderIntput input)
        {
            var list = new PageResultDto<SalesOrderOutput>() { TotalCount = 0, TotalPage = 0, Data = new List<SalesOrderOutput>() };

            var res = (from a in  _salesOrderRepository.GetQueryable()
                       join b in  _productInfoRepository.GetQueryable() on a.productid equals b.productid
                       join c in  _supplierRepository.GetQueryable() on a.SupplierId equals c.SupplierId
                       join d in  _documentsRepository.GetQueryable() on a.documentsid equals d.documentsid
                       join e in  _creatorRepository.GetQueryable() on d.CreatorId equals e.CreatorId 
                       join f in  _userRepository.GetQueryable() on d.CreatorId equals f.userId 
                       join h in  _salesRepository.GetQueryable() on a.SalesId equals h.SalesId 
                       join i in  _accountRepository.GetQueryable() on a.AccountId equals i.AccountId
                       join j in  _customerRepository.GetQueryable() on c.SupplierId equals j.customerid
                       where !a.IsDeleted
                       select new SalesOrderOutput
                       {
                           salesOrdersid=a.salesOrdersid,
                           customer = j.customer,
                           supplierId = j.customerid,
                           Documentnumber =a.Documentnumber,
                           DocumentTime=a.DocumentTime,
                           product_name = b.product_name,
                           CreatorId=f.userId,
                           CreatorName=f.login_name,
                           number=a.number,
                           total_price = d.total_price,
                           deposit = a.deposit,
                           status=d.status,
                           remark=d.remark,
                           SalesName=h.SalesName,
                           discount=d.discount,
                           purchase_status=a.purchase_status,
                           Payoutoffers=a.Payoutoffers,
                           discount_last_money=d.discount_last_money,
                           name=i.name,
                           taxtotal=a.taxtotal


                       });
            if (!string.IsNullOrEmpty(input.documentnumber))
            {
                res = res.Where(x => x.Documentnumber.Contains( input.documentnumber));
            }
            if (!string.IsNullOrEmpty(input.product_name))
            {
                res = res.Where(x => x.product_name.Contains(input.product_name));
            }
            if (!string.IsNullOrEmpty(input.kdate))
            {
                res = res.Where(x => x.DocumentTime >= DateTime.Parse(input.kdate));
            }
            if (!string.IsNullOrEmpty(input.edate))
            {
                res = res.Where(x => x.DocumentTime < DateTime.Parse(input.edate));
            }
            if (input.supplierId >0)
            {
                res = res.Where(x => x.supplierId== input.supplierId);
            }
            if (input.creatorId > 0)
            {
                res = res.Where(x => x.CreatorId == input.creatorId);
            }
            if (input.status != null)
            {
                res = res.Where(x => x.status == input.status);
            }
            if (!string.IsNullOrEmpty(input.remark))
            {
                res = res.Where(x => x.remark.Contains(input.remark));
            }
            list.TotalCount = res.Count();
            list.TotalPage = (int)Math.Ceiling(list.TotalCount * 1.0 / input.pageSize);
            list.Data = res.OrderByDescending(x => x.salesOrdersid).Skip((input.pageIndex - 1) * input.pageSize).Take(input.pageSize).ToList();
            return list;
            
        }
        /// <summary>
        ///销售订单逻辑删除
        /// </summary>
        /// <param name="salesOrdersid"></param>
        /// <returns></returns>
        [HttpGet]
        public async Task<int> DeleteSalesOrder(int salesOrdersid)
        {
            var list = await _salesOrderRepository.GetAsync(salesOrdersid);
            list.IsDeleted = true;
            return await _salesOrderRepository.UpdateAsync(list);
        }
       /// <summary>
       /// 销售订单批量删除
       /// </summary>
       /// <param name="ids"></param>
       /// <returns></returns>
        [HttpPost]
        public async Task<int> RangeDeleteSalesOrder(List<int> ids)
        {
            var list = await _salesOrderRepository.GetAllAsyncList(x => ids.Contains(x.salesOrdersid));
            foreach (var item in list)
            {
                item.IsDeleted = true;
            }
            return await _salesOrderRepository.UpdateRangeAsync(list);
        }


        /// <summary>
        /// 销售出库列表
        /// </summary>
        /// <param name="input"></param>
        /// <returns></returns>
        [HttpPost]
        [SkipCustomFilterAttribute]
        public async Task<PageResultDto<SalesOutboundOutput>> GetSalesOutboundShow(SalesOutboundIntput input)
        {
            var list = new PageResultDto<SalesOutboundOutput>() { TotalCount = 0, TotalPage = 0, Data = new List<SalesOutboundOutput>() };

            var res = (from a in  _salesOrderRepository.GetQueryable()
                       join b in  _productInfoRepository.GetQueryable() on a.productid equals b.productid
                       join c in  _supplierRepository.GetQueryable() on a.SupplierId equals c.SupplierId
                       join d in  _documentsRepository.GetQueryable() on a.documentsid equals d.documentsid
                       join e in  _creatorRepository.GetQueryable() on d.CreatorId equals e.CreatorId
                       join h in  _userRepository.GetQueryable() on d.CreatorId equals h.userId

                       join f in  _warehouseRepository.GetQueryable() on a.depot_id equals f.depot_id
                       join g in  _accountRepository.GetQueryable() on a.AccountId equals g.AccountId
                       join j in  _customerRepository.GetQueryable() on c.SupplierId equals j.customerid
                       join k in  _salesRepository.GetQueryable() on a.SalesId equals k.SalesId

                       where !a.IsDeleted
                       select new SalesOutboundOutput
                       {
                           salesOrdersid = a.salesOrdersid,
                           customer = j.customer,
                           supplierId = j.customerid,
                           Documentnumber = a.Documentnumber,
                           DocumentTime = a.DocumentTime,
                           product_name = b.product_name,
                           CreatorId = h.userId,
                           CreatorName = h.login_name,
                           number = a.number,
                           total_price = d.total_price,
                           deposit = a.deposit,
                           status = d.status,
                           remark = d.remark,
                           Collection=a.Collection,
                           taxtotal=a.taxtotal,
                           Chargeamount=a.Chargeamount,
                           depot_id = f.depot_id,
                           link_number=d.link_number,
                           AccountId=g.AccountId,
                           SalesName=k.SalesName,
                           name=g.name,
                           debt=a.debt,
                       });
            if (!string.IsNullOrEmpty(input.documentnumber))
            {
                res = res.Where(x => x.Documentnumber .Contains( input.documentnumber));
            }
            if (!string.IsNullOrEmpty(input.product_name))
            {
                res = res.Where(x => x.product_name.Contains(input.product_name));
            }
            if (!string.IsNullOrEmpty(input.kdate))
            {
                res = res.Where(x => x.DocumentTime >= DateTime.Parse(input.kdate));
            }
            if (!string.IsNullOrEmpty(input.edate))
            {
                res = res.Where(x => x.DocumentTime < DateTime.Parse(input.edate));
            }
            if (input.supplierId !=null )
            {
                res = res.Where(x => x.supplierId == input.supplierId);
            }
            if (input.creatorId !=null )
            {
                res = res.Where(x => x.CreatorId == input.creatorId);
            }
            if (input.status != null)
            {
                res = res.Where(x => x.status == input.status);
            }
            if (!string.IsNullOrEmpty(input.remark))
            {
                res = res.Where(x => x.remark.Contains(input.remark));
            }
            
            if (!string.IsNullOrEmpty(input.link_number))
            {
                res = res.Where(x => x.link_number.Contains(input.link_number));
            }


            if (input.depot_id !=null )
            {
                res = res.Where(x => x.depot_id == input.depot_id);
            }
            if (!string.IsNullOrEmpty(input.link_number))
            {
                res = res.Where(x => x.link_number.Contains(input.link_number));
            }

            if (input.accountId !=null )
            {
                res = res.Where(x => x.AccountId == input.accountId);
            }

            list.TotalCount = res.Count();
            list.TotalPage = (int)Math.Ceiling(list.TotalCount * 1.0 / input.pageSize);
            list.Data = res.OrderByDescending(x => x.salesOrdersid).Skip((input.pageIndex - 1) * input.pageSize).Take(input.pageSize).ToList().ToList();
            return list;

        }
        /// <summary>
        /// 仓库信息下拉框
        /// </summary>
        /// <returns></returns>
        [HttpGet]
        [SkipCustomFilterAttribute]
        public async Task<IQueryable<Warehouse>> GetWarehouseList()
        {
           var list= await _warehouseRepository.GetAllAsync();
            return list.AsQueryable();
        }

        /// <summary>
        /// 账户信息下拉框
        /// </summary>
        /// <returns></returns>
        [HttpGet]
        [SkipCustomFilterAttribute]
        public async Task<IQueryable<Account>> GetAccountList()
        {
           var list= await _accountRepository.GetAllAsync();
            return list.AsQueryable();
        }
        /// <summary>
        ///销售出库逻辑删除
        /// </summary>
        /// <param name="salesOrdersid"></param>
        /// <returns></returns>
        [HttpGet]
        public async Task<int> DeleteSalesOutbound(int salesOrdersid)
        {
            var list = await _salesOrderRepository.GetAsync(salesOrdersid);
            list.IsDeleted = true;
            return await _salesOrderRepository.UpdateAsync(list);
        }
        /// <summary>
        /// 销售出库批量删除 
        /// </summary>
        /// <param name="ids"></param>
        /// <returns></returns>
        [HttpPost]
        public async Task<int> RangeDeleteSalesOutbound(List<int> ids)
        {
            var list = await _salesOrderRepository.GetAllAsyncList(x => ids.Contains(x.salesOrdersid));
            foreach (var item in list)
            {
                item.IsDeleted = true;
            }
            return await _salesOrderRepository.UpdateRangeAsync(list);
        }
       
        /// <summary>
        /// 销售退货列表
        /// </summary>
        /// <param name="input"></param>
        /// <returns></returns>
        [HttpPost]
        [SkipCustomFilterAttribute]
        public async Task<PageResultDto<SalesReturnOutput>> GetSalesReturnShow(SalesReturnIntput input)
        {
            var list = new PageResultDto<SalesReturnOutput>() { TotalCount = 0, TotalPage = 0, Data = new List<SalesReturnOutput>() };

            var res = (from a in  _salesOrderRepository.GetQueryable()
                       join b in  _productInfoRepository.GetQueryable() on a.productid equals b.productid
                       join c in  _supplierRepository.GetQueryable() on a.SupplierId equals c.SupplierId
                       join d in  _documentsRepository.GetQueryable() on a.documentsid equals d.documentsid
                       join e in  _creatorRepository.GetQueryable() on d.CreatorId equals e.CreatorId
                       join h in  _userRepository.GetQueryable() on d.CreatorId equals h.userId

                       join f in  _warehouseRepository.GetQueryable() on a.depot_id equals f.depot_id
                       join g in  _accountRepository.GetQueryable() on a.AccountId equals g.AccountId
                       join j in  _customerRepository.GetQueryable() on c.SupplierId equals j.customerid
                       join k in  _salesRepository.GetQueryable() on a.SalesId equals k.SalesId
                       where !a.IsDeleted
                       select new SalesReturnOutput
                       {
                           salesOrdersid = a.salesOrdersid,
                           customer = j.customer,
                           supplierId = j.customerid,
                           Documentnumber = a.Documentnumber,
                           DocumentTime = a.DocumentTime,
                           product_name = b.product_name,
                           CreatorId = h.userId,
                           CreatorName = h.login_name,
                           number = a.number,
                           total_price = d.total_price,
                           deposit = a.deposit,
                           status = d.status,
                           remark = d.remark,
                           Collection = a.Collection,
                           taxtotal = a.taxtotal,
                           Chargeamount = a.Chargeamount,
                           depot_id = f.depot_id,
                           link_number = d.link_number,
                           AccountId = g.AccountId,
                           refund=a.refund,
                           debt=a.debt,
                           Refundamount=a.Refundamount,
                           Associateddocuments=a.Associateddocuments,
                           SalesName = k.SalesName,
                           name=g.name
                       });
            if (!string.IsNullOrEmpty(input.documentnumber))
            {
                res = res.Where(x => x.Documentnumber.Contains(input.documentnumber));
            }
            if (!string.IsNullOrEmpty(input.product_name))
            {
                res = res.Where(x => x.product_name.Contains(input.product_name));
            }
            if (!string.IsNullOrEmpty(input.kdate))
            {
                res = res.Where(x => x.DocumentTime >= DateTime.Parse(input.kdate));
            }
            if (!string.IsNullOrEmpty(input.edate))
            {
                res = res.Where(x => x.DocumentTime < DateTime.Parse(input.edate));
            }
            if (input.supplierId != null)
            {
                res = res.Where(x => x.supplierId == input.supplierId);
            }
            if (input.creatorId != null)
            {
                res = res.Where(x => x.CreatorId == input.creatorId);
            }
            if (input.status != null)
            {
                res = res.Where(x => x.status == input.status);
            }
            if (!string.IsNullOrEmpty(input.remark))
            {
                res = res.Where(x => x.remark.Contains(input.remark));
            }

            if (!string.IsNullOrEmpty(input.associateddocuments))
            {
                res = res.Where(x => x.Associateddocuments.Contains(input.associateddocuments));
            }


            if (input.depot_id != null)
            {
                res = res.Where(x => x.depot_id == input.depot_id);
            }
        

            if (input.accountId != null)
            {
                res = res.Where(x => x.AccountId == input.accountId);
            }

            list.TotalCount = res.Count();
            list.TotalPage = (int)Math.Ceiling(list.TotalCount * 1.0 / input.pageSize);
            list.Data = res.OrderByDescending(x => x.salesOrdersid).Skip((input.pageIndex - 1) * input.pageSize).Take(input.pageSize).ToList().ToList();
            return list;

        }


        /// <summary>
        ///销售退货逻辑删除
        /// </summary>
        /// <param name="salesOrdersid"></param>
        /// <returns></returns>
        [HttpGet]
        public async Task<int> DeleteSalesReturn(int salesOrdersid)
        {
            var list = await _salesOrderRepository.GetAsync(salesOrdersid);
            list.IsDeleted = true;
            return await _salesOrderRepository.UpdateAsync(list);
        }
        /// <summary>
        /// 销售退货批量删除 
        /// </summary>
        /// <param name="ids"></param>
        /// <returns></returns>
        [HttpPost]
        public async Task<int> RangeDeleteSalesReturn(List<int> ids)
        {
            var list = await _salesOrderRepository.GetAllAsyncList(x => ids.Contains(x.salesOrdersid));
            foreach (var item in list)
            {
                item.IsDeleted = true;
            }
            return await _salesOrderRepository.UpdateRangeAsync(list);
       }

        /// <summary>
        /// 销售订单导出
        /// </summary>
        /// <returns></returns>
        [HttpGet]
        public async Task<object> SalesOrderExlsx()
        {
            IWorkbook book =new XSSFWorkbook();
            ISheet sheet=book.CreateSheet();
            IRow row = sheet.CreateRow(0);
            row.CreateCell(0).SetCellValue("销售订单Id");
            row.CreateCell(1).SetCellValue("客户名称");
            row.CreateCell(2).SetCellValue("单据编号");
          //  row.CreateCell(3).SetCellValue("单据时间");
            row.CreateCell(3).SetCellValue("商品信息");
            row.CreateCell(4).SetCellValue("操作人");
            row.CreateCell(5).SetCellValue("数量");
            row.CreateCell(6).SetCellValue("合计金额");
            row.CreateCell(7).SetCellValue("含税合计");
            row.CreateCell(8).SetCellValue("收取定金");
            row.CreateCell(9).SetCellValue("状态");
            int index = 0;
             var list= (from a in _salesOrderRepository.GetQueryable()
                        join b in _productInfoRepository.GetQueryable() on a.productid equals b.productid
                        join c in _supplierRepository.GetQueryable() on a.SupplierId equals c.SupplierId
                        join d in _documentsRepository.GetQueryable() on a.documentsid equals d.documentsid
                        join e in _creatorRepository.GetQueryable() on d.CreatorId equals e.CreatorId
                        join f in _userRepository.GetQueryable() on d.CreatorId equals f.userId
                        join h in _salesRepository.GetQueryable() on a.SalesId equals h.SalesId
                        join i in _accountRepository.GetQueryable() on a.AccountId equals i.AccountId
                        join j in _customerRepository.GetQueryable() on c.SupplierId equals j.customerid
                        where !a.IsDeleted
                        select new 
                        {
                            salesOrdersid = a.salesOrdersid,
                            customer = j.customer,
                            supplierId = j.customerid,
                            Documentnumber = a.Documentnumber,
                            DocumentTime = a.DocumentTime,
                            product_name = b.product_name,
                            CreatorId = f.userId,
                            CreatorName = f.login_name,
                            number = a.number,
                            total_price = d.total_price,
                            deposit = a.deposit,
                            status = d.status,
                            remark = d.remark,
                            SalesName = h.SalesName,
                            discount = d.discount,
                            purchase_status = a.purchase_status,
                            Payoutoffers = a.Payoutoffers,
                            discount_last_money = d.discount_last_money,
                            name = i.name,
                            taxtotal = a.taxtotal


                        });
            foreach (var item in list)
            {
                index++;
                row = sheet.CreateRow(index);
                row=sheet.GetRow(index);
                row.CreateCell(0).SetCellValue(item.salesOrdersid);
                row.CreateCell(1).SetCellValue(item.customer);
                row.CreateCell(2).SetCellValue(item.Documentnumber);
               // row.CreateCell(3).SetCellValue((item.DocumentTime).ToString("yyyy-MM-dd HH:mm:ss"));
                row.CreateCell(3).SetCellValue(item.product_name);
                row.CreateCell(4).SetCellValue(item.CreatorName);
                row.CreateCell(5).SetCellValue(item.number == null ? "" : item.number.Value.ToString("0.00"));
                row.CreateCell(6).SetCellValue((double)item.total_price);
                row.CreateCell(7).SetCellValue((double)item.taxtotal);
                row.CreateCell(8).SetCellValue((double)item.deposit);
                row.CreateCell(9).SetCellValue(item.status==true?"启用":"禁用");
                

            }
            byte[] bytes = null;
            using(MemoryStream ms = new MemoryStream())
            {
                book.Write(ms);
                bytes = ms.ToArray();
            }
            string contenType = new FileExtensionContentTypeProvider().Mappings[".xlsx"];
            string fileName = "销售订单信息.xlsx";
            return File(bytes, contenType, fileName);

        }

        /// <summary>
        /// 销售出库导出
        /// </summary>
        /// <returns></returns>
        [HttpGet]
        public async Task<object> SalesOutboundExlsx()
        {
            IWorkbook book = new XSSFWorkbook();
            ISheet sheet = book.CreateSheet();
            IRow row = sheet.CreateRow(0);
          
            row.CreateCell(0).SetCellValue("客户名称");
            row.CreateCell(1).SetCellValue("单据编号");
            row.CreateCell(2).SetCellValue("商品信息");
            row.CreateCell(3).SetCellValue("操作人");
            row.CreateCell(4).SetCellValue("数量");
            row.CreateCell(5).SetCellValue("合计金额");
            row.CreateCell(6).SetCellValue("含税合计");
            row.CreateCell(7).SetCellValue("待收定金");
            row.CreateCell(8).SetCellValue("本次收款");
            row.CreateCell(9).SetCellValue("本次欠款");
            int index = 0;
            var list = (from a in _salesOrderRepository.GetQueryable()
                        join b in _productInfoRepository.GetQueryable() on a.productid equals b.productid
                        join c in _supplierRepository.GetQueryable() on a.SupplierId equals c.SupplierId
                        join d in _documentsRepository.GetQueryable() on a.documentsid equals d.documentsid
                        join e in _creatorRepository.GetQueryable() on d.CreatorId equals e.CreatorId
                        join h in _userRepository.GetQueryable() on d.CreatorId equals h.userId

                        join f in _warehouseRepository.GetQueryable() on a.depot_id equals f.depot_id
                        join g in _accountRepository.GetQueryable() on a.AccountId equals g.AccountId
                        join j in _customerRepository.GetQueryable() on c.SupplierId equals j.customerid
                        join k in _salesRepository.GetQueryable() on a.SalesId equals k.SalesId

                        where !a.IsDeleted
                        select new 
                        {
                            salesOrdersid = a.salesOrdersid,
                            customer = j.customer,
                            supplierId = j.customerid,
                            Documentnumber = a.Documentnumber,
                            DocumentTime = a.DocumentTime,
                            product_name = b.product_name,
                            CreatorId = h.userId,
                            CreatorName = h.login_name,
                            number = a.number,
                            total_price = d.total_price,
                            deposit = a.deposit,
                            status = d.status,
                            remark = d.remark,
                            Collection = a.Collection,
                            taxtotal = a.taxtotal,
                            Chargeamount = a.Chargeamount,
                            depot_id = f.depot_id,
                            link_number = d.link_number,
                            AccountId = g.AccountId,
                            SalesName = k.SalesName,
                            name = g.name,
                            debt=a.debt
                        });
            foreach (var item in list)
            {
                index++;
                row = sheet.CreateRow(index);
                row = sheet.GetRow(index);
                
                row.CreateCell(0).SetCellValue(item.customer);
                row.CreateCell(1).SetCellValue(item.Documentnumber);
                row.CreateCell(2).SetCellValue(item.product_name);
                row.CreateCell(3).SetCellValue(item.CreatorName);
                row.CreateCell(4).SetCellValue(item.number == null ? "" : item.number.Value.ToString("0.00"));
                row.CreateCell(5).SetCellValue((double)item.total_price);
                row.CreateCell(6).SetCellValue((double)item.taxtotal);
                row.CreateCell(7).SetCellValue((double)item.Chargeamount);
                row.CreateCell(8).SetCellValue((double)item.Collection);
                row.CreateCell(9).SetCellValue((double)item.debt);


            }
            byte[] bytes = null;
            using (MemoryStream ms = new MemoryStream())
            {
                book.Write(ms);
                bytes = ms.ToArray();
            }
            string contenType = new FileExtensionContentTypeProvider().Mappings[".xlsx"];
            string fileName = "销售出库信息.xlsx";
            return File(bytes, contenType, fileName);

        }
     

        /// <summary>
        /// 销售退货导出
        /// </summary>
        /// <returns></returns>
        [HttpGet]
        public async Task<object> SalesReturnExlsx()
        {
            IWorkbook book = new XSSFWorkbook();
            ISheet sheet = book.CreateSheet();
            IRow row = sheet.CreateRow(0);
            row.CreateCell(0).SetCellValue("客户名称");
            row.CreateCell(1).SetCellValue("单据编号");
            row.CreateCell(2).SetCellValue("商品信息");
            row.CreateCell(3).SetCellValue("操作人");
            row.CreateCell(4).SetCellValue("数量");
            row.CreateCell(5).SetCellValue("合计金额");
            row.CreateCell(6).SetCellValue("含税合计");
            row.CreateCell(7).SetCellValue("待退金额");
            row.CreateCell(8).SetCellValue("本次退款");
            row.CreateCell(9).SetCellValue("本次欠款");
            int index = 0;
            var list = (from a in _salesOrderRepository.GetQueryable()
                        join b in _productInfoRepository.GetQueryable() on a.productid equals b.productid
                        join c in _supplierRepository.GetQueryable() on a.SupplierId equals c.SupplierId
                        join d in _documentsRepository.GetQueryable() on a.documentsid equals d.documentsid
                        join e in _creatorRepository.GetQueryable() on d.CreatorId equals e.CreatorId
                        join h in _userRepository.GetQueryable() on d.CreatorId equals h.userId

                        join f in _warehouseRepository.GetQueryable() on a.depot_id equals f.depot_id
                        join g in _accountRepository.GetQueryable() on a.AccountId equals g.AccountId
                        join j in _customerRepository.GetQueryable() on c.SupplierId equals j.customerid
                        join k in _salesRepository.GetQueryable() on a.SalesId equals k.SalesId
                        where !a.IsDeleted
                        select new 
                        {
                            salesOrdersid = a.salesOrdersid,
                            customer = j.customer,
                            supplierId = j.customerid,
                            Documentnumber = a.Documentnumber,
                            DocumentTime = a.DocumentTime,
                            product_name = b.product_name,
                            CreatorId = h.userId,
                            CreatorName = h.login_name,
                            number = a.number,
                            total_price = d.total_price,
                            deposit = a.deposit,
                            status = d.status,
                            remark = d.remark,
                            Collection = a.Collection,
                            taxtotal = a.taxtotal,
                            Chargeamount = a.Chargeamount,
                            depot_id = f.depot_id,
                            link_number = d.link_number,
                            AccountId = g.AccountId,
                            refund = a.refund,
                            debt = a.debt,
                            Refundamount = a.Refundamount,
                            Associateddocuments = a.Associateddocuments,
                            SalesName = k.SalesName,
                            name = g.name
                        });
            foreach (var item in list)
            {
                index++;
                row = sheet.CreateRow(index);
                row = sheet.GetRow(index);

                row.CreateCell(0).SetCellValue(item.customer);
                row.CreateCell(1).SetCellValue(item.Documentnumber);
                row.CreateCell(2).SetCellValue(item.product_name);
                row.CreateCell(3).SetCellValue(item.CreatorName);
                row.CreateCell(4).SetCellValue(item.number == null ? "" : item.number.Value.ToString("0.00"));
                row.CreateCell(5).SetCellValue((double)item.total_price);
                row.CreateCell(6).SetCellValue((double)item.taxtotal);
                row.CreateCell(7).SetCellValue((double)item.Refundamount);
                row.CreateCell(8).SetCellValue((double)item.refund);
                row.CreateCell(9).SetCellValue((double)item.debt);
            }
            byte[] bytes = null;
            using (MemoryStream ms = new MemoryStream())
            {
                book.Write(ms);
                bytes = ms.ToArray();
            }
            string contenType = new FileExtensionContentTypeProvider().Mappings[".xlsx"];
            string fileName = "销售退货信息.xlsx";
            return File(bytes, contenType, fileName);

        }
        /// <summary>
        /// 销售统计
        /// </summary>
        /// <returns></returns>
        public async Task<dynamic> GetSalesCount()
        {
            var list = _salesOrderRepository.GetQueryable();
            return list.GroupBy(c => new
            {
                Year = c.DocumentTime.HasValue ? c.DocumentTime.Value.Year : 0,
                Month = c.DocumentTime.HasValue ? c.DocumentTime.Value.Month : 0
            }).Select(c => new
            {
                documentTime=c.Key.Year+"-"+c.Key.Month.ToString().PadLeft(2,'0'),
                collection=c.Sum(y=>y.Collection)
            }).ToList();
            
        }
        /// <summary>
        /// 采购统计
        /// </summary>
        /// <returns></returns>
        public async Task<dynamic> GetPurchaseCount()
        {
            var list=_purchaseOrderRepository.GetQueryable();
            return list.GroupBy(c => new
            {
                Year = c.document_date.HasValue ? c.document_date.Value.Year : 0,
                Month = c.document_date.HasValue ? c.document_date.Value.Month : 0

            }).Select(c => new
            {
                document_date=c.Key.Year+"-"+c.Key.Month.ToString().PadLeft(2,'0'),
                purchase_decimal=c.Sum(y=>y.purchase_decimal)

            }).ToList() ;

        }







    }
    
}
